const mysql = require("mysql");
let config = require("../config/default");
const schedule = require("node-schedule");
//创建了一个数据库连接
const db = mysql.createConnection({
  host: config.database.HOST,
  user: config.database.USER,
  password: config.database.PASSWORD,
});

//创建了一个连接池。连接指定数据库
const pool = mysql.createPool({
  host: config.database.HOST,
  user: config.database.USER,
  password: config.database.PASSWORD,
  database: config.database.WALL,
});

//bdbs 函数使用了一个单独的数据库连接，

let bdbs = (sql, values) => {
  return new Promise((resolve, reject) => {
    db.query(sql, values, (err, result) => {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
};

let query = (sql, values) => {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      if (err) {
        reject(err);
      } else {
        connection.query(sql, values, (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
          //释放该链接，把该链接放回池里供其他人使用
          connection.release();
        });
      }
    });
  });
};
// 创建数据库
//这条SQL语句用来创建一个名为wallts的数据库。如果这个数据库已经存在，那么它不会再次创建。
//指定了数据库的字符集为utf8，并使用utf8_general_ci排序规则
let WALLTS =
  "create database if not exists wallts default charset utf8 collate utf8_general_ci";

let createDatabase = (db) => {
  return bdbs(db, []);
};

//创建一个用户表
let users = `CREATE TABLE IF NOT EXISTS users (
  userid INT NOT NULL AUTO_INCREMENT COMMENT '用户id',
  username VARCHAR(100) DEFAULT '用户名正在审核中' COMMENT '用户名',
  password VARCHAR(100) NOT NULL COMMENT '用户密码',
  email VARCHAR(255) COMMENT '用户邮箱',
  registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  is_banned BOOLEAN DEFAULT FALSE COMMENT '是否封禁',
  branch VARCHAR(100) DEFAULT '主留言墙' COMMENT '用户所属的留言墙',
  total_posts INT DEFAULT 0 COMMENT '总发帖数',
  daily_posts INT DEFAULT 0 COMMENT '每日发帖数',
  following_count INT DEFAULT 0 COMMENT '关注的数量',
  follower_count INT DEFAULT 0 COMMENT '被关注的数量',
  permission VARCHAR(255) DEFAULT 'Level3' COMMENT '用户权限',
  avatar VARCHAR(255) DEFAULT '默认.png' COMMENT '用户头像',
  background VARCHAR(255) DEFAULT '默认.png' COMMENT '用户背景',
  bio VARCHAR(255) DEFAULT '这个人好像没什么个性' COMMENT '个性签名',
  change_count INT DEFAULT 0 COMMENT '更换次数',
  total_comments INT DEFAULT 0 COMMENT '总评论数',
  daily_comments INT DEFAULT 0 COMMENT '每日发布的评论数',
  campus_auth VARCHAR(255) COMMENT '校园认证',
  c_auth VARCHAR(255) COMMENT 'C认证',
  avatar_username BOOLEAN DEFAULT FALSE COMMENT '用户名称审核状态',
  avatar_reviewed BOOLEAN DEFAULT FALSE COMMENT '用户头像审核状态',
  background_reviewed BOOLEAN DEFAULT FALSE COMMENT '用户背景审核状态',
  bio_reviewed BOOLEAN DEFAULT FALSE COMMENT '个性签名审核状态',
  PRIMARY KEY (userid)
);`;

let system_messages = `CREATE TABLE IF NOT EXISTS system_messages (
  message_id INT NOT NULL AUTO_INCREMENT COMMENT '消息id',
  message_content VARCHAR(255) NOT NULL COMMENT '消息内容',
  receiver_id INT NOT NULL COMMENT '接收者的用户id',
  creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '消息创建时间',
  is_retrieved BOOLEAN NOT NULL DEFAULT true COMMENT '是否获取该条系统消息',
  PRIMARY KEY (message_id),
  FOREIGN KEY (receiver_id) REFERENCES users (userid)
);`

//创建一个关系表
let follows = `CREATE TABLE IF NOT EXISTS follows (
  id INT NOT NULL AUTO_INCREMENT COMMENT '关注记录ID',
  follower_id INT NOT NULL COMMENT '关注者ID',
  followed_id INT NOT NULL COMMENT '被关注者ID',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '关注时间',
  is_retrieved BOOLEAN DEFAULT true COMMENT '是否获取该条消息',
  PRIMARY KEY (id),
  FOREIGN KEY (follower_id) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (followed_id) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE,
  INDEX (follower_id),
  INDEX (followed_id),
  UNIQUE KEY unique_follow (follower_id, followed_id)
)`;

//创建发布的帖子表
const posts = `CREATE TABLE IF NOT EXISTS posts (
  postid INT NOT NULL AUTO_INCREMENT COMMENT '帖子id',
  userid INT NOT NULL COMMENT '用户id',
  username VARCHAR(255) NOT NULL COMMENT '用户名称',
  type VARCHAR(255) DEFAULT null COMMENT '帖子类型',
  color VARCHAR(255) DEFAULT 'rgba(252,175,162,1)' COMMENT '发布帖子所选择的颜色',
  content TEXT NOT NULL COMMENT '帖子内容',
  image_name VARCHAR(255) DEFAULT null COMMENT '图片名称',
  image_path VARCHAR(255) DEFAULT null COMMENT '图片路径',
  post_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
  like_count INT DEFAULT 0 COMMENT '喜欢次数',
  report_count INT DEFAULT 0 COMMENT '举报次数',
  comment_count INT DEFAULT 0 COMMENT '评论次数',
  branch VARCHAR(100) DEFAULT '主留言墙' COMMENT '帖子所属的留言墙',
  is_approved BOOLEAN DEFAULT false COMMENT '是否通过审核',
  PRIMARY KEY (postid),
  FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE
)`;

// 创建评论表
const comments = `CREATE TABLE IF NOT EXISTS comments (
  commentid INT NOT NULL AUTO_INCREMENT COMMENT '评论id',
  userid INT NOT NULL COMMENT '用户id',
  useredid INT NOT NULL COMMENT '被评论用户id',
  username VARCHAR(255) NOT NULL COMMENT '用户名',
  postid INT NOT NULL COMMENT '帖子id',
  content TEXT NOT NULL COMMENT '评论内容',
  comment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间',
  PRIMARY KEY (commentid),
  is_retrieved BOOLEAN NOT NULL DEFAULT false COMMENT '是否获取该条评论',
  is_message BOOLEAN NOT NULL DEFAULT false COMMENT '是否审核通过该评论',
  
  FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (useredid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (postid) REFERENCES posts(postid) ON UPDATE CASCADE ON DELETE CASCADE
);`;

// 创建反馈表
const feedbacks = `CREATE TABLE IF NOT EXISTS feedbacks (
  feedbacksid INT NOT NULL AUTO_INCREMENT COMMENT '反馈id',
  userid INT NOT NULL COMMENT '用户id',
  useredid INT NOT NULL COMMENT '被反馈的用户id',
  postid INT NOT NULL COMMENT '帖子id',
  is_like BOOLEAN DEFAULT FALSE COMMENT '是否喜欢',
  is_report BOOLEAN DEFAULT FALSE COMMENT '是否举报',
  feedbacks_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
  is_retrieved BOOLEAN NOT NULL DEFAULT true COMMENT '是否获取该条反馈',
  PRIMARY KEY (userid,postid),
  UNIQUE KEY(feedbacksid),
  FOREIGN KEY (useredid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (userid) REFERENCES users(userid) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (postid) REFERENCES posts(postid) ON UPDATE CASCADE ON DELETE CASCADE
)`;

let createTable = (sql) => {
  return query(sql, []);
};

async function create() {
  await createDatabase(WALLTS);
  await createTable(users);
  await createTable(posts);
  await createTable(feedbacks);
  await createTable(follows);
  await createTable(comments);
  await createTable(system_messages);
}
create();

schedule.scheduleJob("0 0 * * *", function () {
  // 将所有用户的daily_posts字段重置为0
  let sql = `UPDATE users SET daily_posts = 0`;
  query(sql);
});
schedule.scheduleJob("0 0 * * *", function () {
  // 将所有用户的daily_posts字段重置为0
  let sql = `UPDATE users SET daily_comments = 0`;
  query(sql);
});
schedule.scheduleJob("0 0 1 * *", function () {
  // 在每月初执行重置操作
  const sql = `UPDATE users SET change_count = 0`;
  query(sql);
});

//定义触发器
let feedbackstriggerinsert = `CREATE TRIGGER update_like_report_count_after_insert
AFTER INSERT ON feedbacks
FOR EACH ROW
BEGIN
    UPDATE posts SET like_count = (SELECT COUNT(*) FROM feedbacks WHERE postid = NEW.postid AND is_like = TRUE) WHERE postid = NEW.postid;
    UPDATE posts SET report_count = (SELECT COUNT(*) FROM feedbacks WHERE postid = NEW.postid AND is_report = TRUE) WHERE postid = NEW.postid;
    UPDATE posts SET comment_count = (SELECT COUNT(*) FROM comments WHERE postid = NEW.postid) WHERE postid = NEW.postid;
    END`;

let feedbackstriggerupdate = `CREATE TRIGGER update_like_report_count_after_update
    AFTER UPDATE ON feedbacks
    FOR EACH ROW
    BEGIN
        UPDATE posts SET like_count = (SELECT COUNT(*) FROM feedbacks WHERE postid = NEW.postid AND is_like = TRUE) WHERE postid = NEW.postid;
        UPDATE posts SET report_count = (SELECT COUNT(*) FROM feedbacks WHERE postid = NEW.postid AND is_report = TRUE) WHERE postid = NEW.postid;
        UPDATE posts SET comment_count = (SELECT COUNT(*) FROM comments WHERE postid = NEW.postid) WHERE postid = NEW.postid;
    END`;

let update_comment_count_after_insert = `CREATE TRIGGER update_comment_count_after_insert
    AFTER INSERT ON comments
    FOR EACH ROW
    BEGIN
        UPDATE posts SET comment_count = (SELECT COUNT(*) FROM comments WHERE postid = NEW.postid) WHERE postid = NEW.postid;
        END`;
//commentstriggerdelete
let update_comment_count_after_delete = `CREATE TRIGGER update_comment_count_after_delete
AFTER DELETE ON comments
FOR EACH ROW
BEGIN
    UPDATE posts SET comment_count = comment_count - 1 WHERE postid = OLD.postid;
END;`;

let update_total_posts_after_insert = `CREATE TRIGGER update_total_posts_after_insert
    AFTER INSERT ON posts
    FOR EACH ROW
    BEGIN
        UPDATE users
        SET total_posts = total_posts + 1
        WHERE userid = NEW.userid;
    END;`;

let update_total_posts_after_delete = `CREATE TRIGGER update_total_posts_after_delete
    AFTER DELETE ON posts
    FOR EACH ROW
    BEGIN
        UPDATE users
        SET total_posts = total_posts - 1
        WHERE userid = OLD.userid;
    END;`;
let update_daily_posts_after_insert = `CREATE TRIGGER update_daily_posts_after_insert
    AFTER INSERT ON posts
    FOR EACH ROW
    BEGIN
        UPDATE users
        SET daily_posts = daily_posts + 1
        WHERE userid = NEW.userid;
    END;`;
let update_daily_posts_after_delete = `CREATE TRIGGER update_daily_posts_after_delete
    AFTER DELETE ON posts
    FOR EACH ROW
    BEGIN
        UPDATE users
        SET daily_posts = daily_posts - 1
        WHERE userid = OLD.userid;
    END;`;

let update_counts_after_insert = `CREATE TRIGGER update_counts_after_insert
    AFTER INSERT ON follows
    FOR EACH ROW
    BEGIN
      UPDATE users SET following_count = following_count + 1 WHERE userid = NEW.follower_id;
      UPDATE users SET follower_count = follower_count + 1 WHERE userid = NEW.followed_id;
    END;`;
let update_counts_after_delete = `CREATE TRIGGER update_counts_after_delete
    AFTER DELETE ON follows
    FOR EACH ROW
    BEGIN
      UPDATE users SET following_count = following_count - 1 WHERE userid = OLD.follower_id;
      UPDATE users SET follower_count = follower_count - 1 WHERE userid = OLD.followed_id;
    END;`;
let update_posts_username = `CREATE TRIGGER update_posts_username
    AFTER UPDATE ON users
    FOR EACH ROW
    BEGIN
      IF NEW.username <> OLD.username THEN
        UPDATE posts
        SET username = NEW.username
        WHERE userid = NEW.userid;
      END IF;
    END;`;

let update_comments_username = `CREATE TRIGGER update_comments_username AFTER UPDATE ON users
              FOR EACH ROW
              BEGIN
                  UPDATE comments
                  SET username = NEW.username
                  WHERE userid = NEW.userid;
              END`;

//删除触发器
// query(`DROP TRIGGER update_like_report_count_after_insert;`)
// query('DROP TRIGGER update_like_report_count_after_update;')
// query('DROP TRIGGER update_comment_count_after_insert;')
// query('DROP TRIGGER update_comment_count_after_update;')
// query('DROP TRIGGER update_total_posts_after_insert;')
// query('DROP TRIGGER update_total_posts_after_delete;')
// query('DROP TRIGGER update_daily_posts_after_insert;')
// query('DROP TRIGGER update_daily_posts_after_delete;')
// query('DROP TRIGGER update_counts_after_insert;')
// query('DROP TRIGGER update_counts_after_delete;')
// query('DROP TRIGGER update_posts_username')
// query('DROP TRIGGER update_comments_username')

// //判断建立触发器
let triggerName1 = "update_like_report_count_after_insert";
let triggerName2 = "update_like_report_count_after_update";
let triggerName3 = "update_comment_count_after_insert";
let triggerName4 = "update_comment_count_after_delete";
let triggerName5 = "update_total_posts_after_insert";
let triggerName6 = "update_total_posts_after_delete";
let triggerName7 = "update_daily_posts_after_insert";
let triggerName8 = "update_daily_posts_after_delete";
let triggerName9 = "update_counts_after_insert";
let triggerName10 = "update_counts_after_delete";
let triggerName11 = "update_posts_username";
let triggerName12 = "update_comments_username";

let sql = `SHOW TRIGGERS`;

// query(sql).then((rows) => {
//   let trigger1Exists = rows.some((row) => row.Trigger === triggerName1);
//   let trigger2Exists = rows.some((row) => row.Trigger === triggerName2);
//   let trigger3Exists = rows.some((row) => row.Trigger === triggerName3);
//   let trigger4Exists = rows.some((row) => row.Trigger === triggerName4);
//   let trigger5Exists = rows.some((row) => row.Trigger === triggerName5);
//   let trigger6Exists = rows.some((row) => row.Trigger === triggerName6);
//   let trigger7Exists = rows.some((row) => row.Trigger === triggerName7);
//   let trigger8Exists = rows.some((row) => row.Trigger === triggerName8);
//   let trigger9Exists = rows.some((row) => row.Trigger === triggerName9);
//   let trigger10Exists = rows.some((row) => row.Trigger === triggerName10);
//   let trigger11Exists = rows.some((row) => row.Trigger === triggerName11);
//   let trigger12Exists = rows.some((row) => row.Trigger === triggerName12);

//   if (!trigger1Exists) {
//     // 触发器1不存在，可以创建它
//     query(feedbackstriggerinsert);
//   } else {
//     // 触发器1已经存在
//   }
//   if (!trigger2Exists) {
//     // 触发器2不存在，可以创建它
//     query(feedbackstriggerupdate);
//   } else {
//     // 触发器2已经存在
//   }
//   if (!trigger3Exists) {
//     // 触发器3不存在，可以创建它
//     query(update_comment_count_after_insert);
//   } else {
//     // 触发器3已经存在
//   }
//   if (!trigger4Exists) {
//     // 触发器4不存在，可以创建它
//     query(update_comment_count_after_delete);
//   } else {
//     // 触发器4已经存在
//   }
//   if (!trigger5Exists) {
//     // 触发器5不存在，可以创建它
//     query(update_total_posts_after_insert);
//   } else {
//     // 触发器5已经存在
//   }
//   if (!trigger6Exists) {
//     // 触发器6不存在，可以创建它
//     query(update_total_posts_after_delete);
//   } else {
//     // 触发器6已经存在
//   }
//   if (!trigger7Exists) {
//     // 触发器7不存在，可以创建它
//     query(update_daily_posts_after_insert);
//   } else {
//     // 触发器7已经存在
//   }
//   if (!trigger8Exists) {
//     // 触发器8不存在，可以创建它
//     query(update_daily_posts_after_delete);
//   } else {
//     // 触发器8已经存在
//   }

//   if (!trigger9Exists) {
//     // 触发器9不存在，可以创建它
//     query(update_counts_after_insert);
//   } else {
//     // 触发器9已经存在
//   }
//   if (!trigger10Exists) {
//     // 触发器10不存在，可以创建它
//     query(update_counts_after_delete);
//   } else {
//     // 触发器10已经存在
//   }
//   if (!trigger11Exists) {
//     // 触发器11不存在，可以创建它
//     query(update_posts_username);
//   } else {
//     // 触发器11已经存在
//   }
//   if (!trigger12Exists) {
//     // 触发器11不存在，可以创建它
//     query(update_comments_username);
//   } else {
//     // 触发器11已经存在
//   }
// });

//新建用户
exports.insertUser = (value) => {
  let _sql = "INSERT INTO users (username, password,email) VALUES (?,?,?)";
  return query(_sql, value);
};

//查询用户账号
exports.selectUser = (value) => {
  let _sql = `SELECT * FROM users WHERE username = ?`;
  return query(_sql, value);
};
//根据邮箱查询用户
exports.selectEmail = (value) => {
  let _sql = `
    SELECT
      *,
      CASE WHEN avatar_username = false THEN '审核中' ELSE username END AS username,
      CASE WHEN avatar_reviewed = false THEN '默认.png' ELSE avatar END AS avatar
    FROM
      users
    WHERE
      email = ?`;
  return query(_sql, value);
};
//更新密码
exports.updatepass = (value) => {
  let _sql = ` UPDATE users SET password = ? WHERE email = ?`;
  return query(_sql, value);
};

//查询搜索posts表的默认卡片数据

exports.showCard = (value) => {
  let _sql = `SELECT 
                posts.*,
                CASE WHEN users.avatar_username = false THEN '审核中' ELSE users.username END AS username
              FROM posts
              LEFT JOIN users ON posts.userid = users.userid
              WHERE posts.branch = ? AND posts.is_approved = true`;
  
  if (value[1]) {
    _sql += ` AND posts.type = ?`;
  }

  return query(_sql, value);
};
//根据postid查询 用户刚刚发布的帖子
exports.selectCard = (value) => {
  let _sql = "SELECT * FROM posts WHERE postid = ?";
  return query(_sql, value);
};
//查询默认卡片的默认评论

exports.showComment = (value) => {
  let _sql = `SELECT 
    comments.*,
    CASE WHEN users.avatar_reviewed = false THEN '默认.png' ELSE users.avatar END AS avatar,
    CASE WHEN users.avatar_username = false THEN '审核中' ELSE users.username END AS username
  FROM comments
  JOIN users ON comments.userid = users.userid
  WHERE comments.postid = ? AND comments.is_message = true
  ORDER BY comments.comment_date ASC`; // 添加 ORDER BY 子句，按照 comment_date 字段的升序排序
  return query(_sql, value);
};
//添加卡片
exports.addCard = (value) => {
  let _sql = `INSERT INTO posts (userid, username, type, color, content, image_name,image_path,branch)
  VALUES (?, ?, ?, ?, ?, COALESCE(?, '默认.png'),?, COALESCE(?, '主留言墙'))`;
  return query(_sql, value);
};

//用户喜爱反馈表

exports.changefeedbacks = (value) => {
  let _sql = `INSERT INTO feedbacks (userid, postid, is_like,useredid) VALUES (?, ?, ?,?)
  ON DUPLICATE KEY UPDATE is_like = ?`;
  return query(_sql, value);
};

//用户对哪些内容点了喜欢
exports.userLike = (value) => {
  let _sql = `SELECT postid FROM feedbacks WHERE userid = ? AND is_like = ?;`;
  return query(_sql, value);
};

//用户举报反馈表

exports.changefeedbacksreport = (value) => {
  let _sql = `INSERT INTO feedbacks (userid, postid, is_report) VALUES (?, ?, ?)
  ON DUPLICATE KEY UPDATE is_report = ?`;
  return query(_sql, value);
};

//用户对哪些内容点了举报
exports.userReport = (value) => {
  let _sql = `SELECT postid FROM feedbacks WHERE userid = ? AND is_report = ?;`;
  return query(_sql, value);
};

//添加新的评论
exports.addCommit = async (value) => {
  let updateSql = `UPDATE users SET total_comments = total_comments + 1, daily_comments = daily_comments + 1 WHERE userid = ?`;

  return query(updateSql, value[0]).then(() => {
    // 更新users表中的总评论字段和每日评论字段加一

    let _sql = `INSERT INTO comments (userid, username, postid, content, useredid) VALUES (?, ?, ?, ?, ?)`;
    // 执行插入评论的语句
    return query(_sql, value);
  });
};

//根据id查询评论 （添加完新的评论，根据返回的内容再查询数据）

exports.selectCommit = (value) => {
  let _sql = `SELECT comments.*, users.avatar
  FROM comments
  JOIN users ON comments.userid = users.userid
  WHERE commentid = ?`;
  return query(_sql, value);
};

//根据userid判断当天用户发了多少条card了
exports.selectCardCount = (value) => {
  let _sql = `SELECT daily_posts FROM users WHERE userid = ?;`;
  return query(_sql, value);
};
//根据postid来查询卡片所属的用户id
exports.selectUserid = (value) => {
  let _sql = `SELECT DISTINCT userid FROM posts WHERE postid = ?;`;
  return query(_sql, value);
};
exports.deleteCard = (value) => {
  let _sql = `DELETE FROM posts WHERE postid = ?;`;
  return query(_sql, value);
};

//通过userid 来获取此用户的权限

exports.selectPermission = (value) => {
  let _sql = `SELECT permission FROM users WHERE userid = ?`;
  return query(_sql, value);
};

//查询branch

exports.selectBranch = (value) => {
  let _sql = `SELECT
  CASE
      WHEN ? IS NOT NULL THEN (SELECT branch FROM users WHERE userid = ?)
      WHEN ? IS NOT NULL THEN (SELECT branch FROM posts WHERE postid = ?)
  END AS branch;`;
  return query(_sql, value);
};
//搜索框搜索卡片
exports.inputSelectCard = (value) => {
  let _sql = `SELECT * FROM posts WHERE
  (? IS NOT NULL AND postid = ? AND (branch = ? OR branch = '主留言墙')) OR
  (? IS NOT NULL AND username = ? AND (branch = ? OR branch = '主留言墙'))`;
  return query(_sql, value);
};

//根据postid得到userid
exports.selectUseridtwo = (value) => {
  let _sql = `select userid from posts where postid = ?`;
  return query(_sql, value);
};
//通过姓名查询userid
exports.selectUseridthree = (value) => {
  let _sql = `select userid from users where username = ?`;
  return query(_sql, value);
};
//查询关注
exports.selectfensi = (value) => {
  let _sql = `SELECT follows.followed_id, users.username, users.avatar
  FROM follows
  INNER JOIN users ON follows.followed_id = users.userid
  WHERE follows.follower_id = ?`;
  return query(_sql, value);
};
//查询粉丝
exports.selectguanzhu = (value) => {
  let _sql = `SELECT follows.follower_id, users.username, users.avatar
              FROM follows
              INNER JOIN users ON follows.follower_id = users.userid
              WHERE follows.followed_id = ?`;
  return query(_sql, value);
};
//关注
exports.guanzhuT = (value) => {
  let _sql = `INSERT INTO follows (follower_id, followed_id)
  VALUES (?, ?);`;
  return query(_sql, value);
};

//取消关注

exports.guanzhuF = (value) => {
  let _sql = `DELETE FROM follows WHERE follower_id = ? AND followed_id = ?;`;
  return query(_sql, value);
};

//用户帖子数量
exports.usercardCount = (value) => {
  let _sql = `SELECT *
  FROM posts
  WHERE userid = ?`;
  return query(_sql, value);
};

//通过postid 来获取图片路径
exports.selectPicturePath = (value) => {
  let _sql = `select image_name from posts where postid = ?`;
  return query(_sql, value);
};

//更新头像
exports.updateAvatar = (value) => {
  let _sql = `UPDATE users SET avatar = ?,avatar_reviewed = false,change_count = change_count + 1 WHERE userid = ?`;
  return query(_sql, value);
};
//查询头像
exports.selectavar = (value) => {
  let _sql = `select avatar from users where userid = ?`;
  return query(_sql, value);
};

//查询用户的所有信息
exports.selectuserall = (value) => {
  let _sql = `SELECT 
                *,
                CASE WHEN avatar_reviewed = false THEN '默认.png' ELSE avatar END AS avatar,
                CASE WHEN background_reviewed = false THEN '默认.png' ELSE background END AS background,
                CASE WHEN bio_reviewed = false THEN '这个人好像没什么个性' ELSE bio END AS bio
              FROM users
              WHERE userid = ?`;
  return query(_sql, value);
};
//更新背景图片
exports.updatebgcpicture = (value) => {
  let _sql = `UPDATE users SET background = ?,background_reviewed = false,change_count = change_count + 1 WHERE userid = ?`;
  return query(_sql, value);
};
//查询背景图片
exports.selectbgcpicture = (value) => {
  let _sql = `select background from users where userid = ? `;
  return query(_sql, value);
};
//更新姓名
exports.updatename = (value) => {
  let _sql = `update users set username = ?,avatar_username = false,change_count = change_count + 1 where userid = ?`;
  return query(_sql, value);
};
//更新个性签名
exports.updatebio = (value) => {
  let _sql = `update users set bio = ?,bio_reviewed = false,change_count = change_count + 1 where userid = ?`;
  return query(_sql, value);
};
//查询改变的次数
exports.change_count = (value) => {
  let _sql = `select change_count from users where userid = ?`;
  return query(_sql, value);
};

//评论消息信息
exports.commentmessage = (value) => {
  let _sql = `SELECT comments.*, users.avatar
  FROM comments
  JOIN users ON comments.userid = users.userid
  WHERE comments.useredid = ? and is_retrieved = true ;`;
  return query(_sql, value);
};

//删除评论消息

exports.deleteemessage = (value) => {
  let _sql = `UPDATE comments SET is_retrieved = false WHERE commentid = ?`;

  return query(_sql, value);
};

//删除评论

exports.deletecomment = async (value) => {
  let updateSql = `UPDATE users SET total_comments = total_comments - 1, daily_comments = daily_comments - 1 WHERE userid = ?`;

  return query(updateSql, value[1]).then(() => {
    // 更新users表中的总评论字段和每日评论字段减一
    let _sql = `DELETE  FROM comments WHERE commentid = ?`;
    return query(_sql, value[0]);
  });
};

//查看每天评论数

exports.dailycount = async (value) => {
  let _sql = `select daily_comments from users where userid = ?`;
  return query(_sql, value);
};

//查询哪些用户对我点了喜欢

exports.likemessagecount = async (value) => {
  let _sql = `SELECT feedbacks.*, users.avatar,users.username
  FROM feedbacks
  JOIN users ON feedbacks.userid = users.userid
  WHERE feedbacks.useredid = ? and is_retrieved = true and is_like = true ;`;

  return query(_sql, value);
};

//删除点赞消息提醒

exports.deletelikemessage = async (value) => {
  let _sql = `UPDATE feedbacks SET is_retrieved = false WHERE feedbacksid = ?`;
  return query(_sql, value);
};

//获取关注消息

exports.fansmessage = async (value) => {
  let _sql = `SELECT users.avatar, users.username, follows.created_at, follows.id 
              FROM follows
              JOIN users ON follows.follower_id = users.userid 
              WHERE follows.followed_id = ? AND follows.is_retrieved = true`;
  return query(_sql, value);
};

//删除关注消息提醒

exports.deletefansmessage = async (value) => {
  let _sql = `UPDATE follows SET is_retrieved = false WHERE id = ?`;
  return query(_sql, value);
};

//通过邮箱来获取用户ID
exports.emailselectuserid = async (value) => {
  let _sql = `select userid from users where email = ?`;
  return query(_sql, value);
}

//查询获取用户信息

exports.selectsystemmessage = async (value) => {
  let _sql = `select * from system_messages where receiver_id = ? and is_retrieved = true`;
  return query(_sql, value);
}

//打上不提示标记

exports.deletesystemmessage = async (value) => {
  let _sql = `update system_messages set is_retrieved = false where message_id = ?`;
  return query(_sql, value)


}


//获取表所有内容

exports.getforms = async (value) => {
  let _sql = `select * from ${value}`

  return query(_sql)
}


//修改内容

exports.changeformvalue = async (value) => {
  if (value[0].formname === 'comments' && value[0].activeTab === 'is_message' && value[1] == 1) {
    // 如果操作的是 comments 表，并且修改的字段是 is_message，同时要将其设置为 1
    // 则同时将 is_retrieved 字段设为 1
    let _sql = `update ${value[0].formname} set ${value[0].activeTab} = ?, is_retrieved = 1 where ${value[0].conditionkey} = ?`;
    return query(_sql, [value[1], value[2]]);
  } else {
    // 其他情况下只修改指定字段的值
    let _sql = `update ${value[0].formname} set ${value[0].activeTab} = ? where ${value[0].conditionkey} = ?`;
    return query(_sql, [value[1], value[2]]);
  }
};
//删除行

exports.deleterowmessage = async (value) => {
  let _sql=`delete from ${value[0].formname} where ${value[0].conditionkey} = ?`
  return query(_sql,value[1])
}

//发送系统消息
exports.sendsystemmessage=async (value) => {
  let _sql =`INSERT INTO system_messages (receiver_id,message_content) values (?,?) `
  return query(_sql,value)
}